package service;

import model.Book;
import serviceDefine.BookService;

import java.rmi.RemoteException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class BookServiceImpl implements BookService {

    static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://localhost:3306/distributed_task3?useSSL=false&serverTimezone=UTC";
    static final String USER = "root";
    static final String PASSWORD = "biao";

    @Override
    public boolean add(Book b) throws RemoteException{
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        int result=0;
        try {

            Class.forName(JDBC_DRIVER);
            connection = DriverManager.getConnection(DB_URL,USER,PASSWORD);

            String sql = "INSERT INTO book(name,author,price) VALUES(?,?,?)";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, b.getName());
            preparedStatement.setString(2, b.getAuthor());
            preparedStatement.setDouble(3, b.getPrice());
            result = preparedStatement.executeUpdate();
        }catch (SQLException se){
            se.printStackTrace();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try {
                if(connection != null) connection.close();
            }catch (SQLException se2){
                se2.printStackTrace();
            }
            try{
                if(preparedStatement != null) preparedStatement.close();
            }catch (SQLException se3){
                se3.printStackTrace();
            }
        }
        if(result!=0){
            System.out.println("add Success!");
            return true;
        }
        return false;
    }

    @Override
    public Book queryById(int bookId) throws RemoteException{
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        Book book = null;
        try{
            //注册驱动
            Class.forName(JDBC_DRIVER);
            connection = DriverManager.getConnection(DB_URL,USER,PASSWORD);

            String sql = "SELECT * FROM book WHERE id = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, bookId);
            ResultSet resultSet = preparedStatement.executeQuery();
            resultSet.next();
            book = new Book(resultSet.getInt("id"),
                                 resultSet.getString("name"),
                                 resultSet.getString("author"),
                                 resultSet.getDouble("price"));
            return book;
        }catch (SQLException se){
            se.printStackTrace();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try {
                if(connection != null) connection.close();
            }catch (SQLException se2){
                se2.printStackTrace();
            }
            try {
                if(preparedStatement != null) preparedStatement.close();
            }catch (SQLException se3){
                se3.printStackTrace();
            }
        }
        return book;
    }

    @Override
    public List<Book> queryByName(String name) throws RemoteException{
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        List<Book> bookList = new ArrayList<>();
        try{

            Class.forName(JDBC_DRIVER);
            connection = DriverManager.getConnection(DB_URL,USER,PASSWORD);
            String sql = "SELECT * FROM book WHERE name=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, name);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                Book book = new Book(resultSet.getInt("id"),
                        resultSet.getString("name"),
                        resultSet.getString("author"),
                        resultSet.getDouble("price"));
                bookList.add(book);
            }
        }catch (SQLException se){
            se.printStackTrace();
        }catch (Exception e){
            e.printStackTrace();
        }finally {

            try {
                if(connection != null) connection.close();
            }catch (SQLException se2){
                se2.printStackTrace();
            }
            try {
                if(preparedStatement != null) preparedStatement.close();
            }catch (SQLException se3){
                se3.printStackTrace();
            }
        }
        return bookList;
    }

    @Override
    public boolean delete(int bookId) throws RemoteException{
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        int result=0;
        try{
            Class.forName(JDBC_DRIVER);
            connection=DriverManager.getConnection(DB_URL,USER,PASSWORD);
            String sql = "DELETE FROM book WHERE id = ?";
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setInt(1, bookId);
            result = preparedStatement.executeUpdate();
        }catch (SQLException se){
            se.printStackTrace();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try {
                if(connection != null) connection.close();
            }catch (SQLException se2){
                se2.printStackTrace();
            }
            try {
                if(preparedStatement != null) preparedStatement.close();
            }catch (SQLException se3){
                se3.printStackTrace();
            }
        }
        return result==0;
    }
}
